using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN SinhVienDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:10/08/2010 58:03:30</para>
    /// <para>Class Name: SinhVienDAO</para>
    /// <para>Description: Class access to table SinhVien in database. Class for coder code here</para>
    ///</summary>


    public partial class SinhVienDAO
    {
        private void CreateFilterSinhVien(List<string> lsFilter, IDbCommand cmd, SinhVienEntity sinhVienEntity)
        {

            if (sinhVienEntity == null) return;
            if (sinhVienEntity.TenSinhVien != null)
            {
                if (sinhVienEntity.TenSinhVien.Length > 0)
                {
                    lsFilter.Add("SinhVien.TenSinhVien Like '%'+@TenSinhVien+'%'");
                    this.CreateCommandParameter(cmd, "@TenSinhVien", sinhVienEntity.TenSinhVien);
                }
            }
            if (sinhVienEntity.SoCMND != null)
            {
                if (sinhVienEntity.SoCMND.Length > 0)
                {
                    lsFilter.Add("SinhVien.SoCMND=@SoCMND");
                    this.CreateCommandParameter(cmd, "@SoCMND", sinhVienEntity.SoCMND);
                }
            }
            if (sinhVienEntity.NgaySinh != null)
            {
                if (sinhVienEntity.NgaySinh.Length > 0)
                {
                    lsFilter.Add("SinhVien.NgaySinh=@NgaySinh");
                    this.CreateCommandParameter(cmd, "@NgaySinh", sinhVienEntity.NgaySinh);
                }
            }
            if (sinhVienEntity.MaSinhVien != null)
            {
                if (sinhVienEntity.MaSinhVien.Length > 0)
                {
                    lsFilter.Add("SinhVien.MaSinhVien=@MaSinhVien");
                    this.CreateCommandParameter(cmd, "@MaSinhVien", sinhVienEntity.MaSinhVien);
                }
            }
            if (sinhVienEntity.GioiTinh <= 1)
            {
                lsFilter.Add("SinhVien.GioiTinh=@GioiTinh");
                this.CreateCommandParameter(cmd, "@GioiTinh", sinhVienEntity.GioiTinh);
            }
            if (sinhVienEntity.GhiChu != null)
            {
                if (sinhVienEntity.GhiChu.Length > 0)
                {
                    lsFilter.Add("SinhVien.GhiChu Like '%'+@GhiChu+'%'");
                    this.CreateCommandParameter(cmd, "@GhiChu", sinhVienEntity.GhiChu);
                }
            }
        }
        public DataSet SelectAllDSSinhVienByListID(List<int> LsSinhVien_Id)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID
                                 FROM          SinhVien LEFT  JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID LEFT JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID LEFT JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID LEFT JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID LEFT JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID LEFT JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID ";

            cmd.CommandText += " WHERE SinhVien.SinhVien_ID IN(" + string.Join(",", LsSinhVien_Id.ConvertAll<string>(Convert.ToString).ToArray()) + ")";
            return this.SelectDS(cmd);
        }

        public DataSet SearchSinhVien(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID ";
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVien_ID(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? @" INNER JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? @"INNER JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " : "") + (Lop_ID > 0 ? @"INNER JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " : "") : "");
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("NganhHoc.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("PhanNganhSinhVien.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("PhanNganhSinhVien.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("LopSinhVien.Lop_ID=" + Lop_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienHocMonHoc(int MonHoc_ID, int NamHoc_ID, int HocKy_ID, int Lop_ID, SinhVienEntity sinhVienEntity)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID
                                 FROM          SinhVien 
                                                         LEFT JOIN PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID 
                                                         LEFT JOIN NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID
                                                         LEFT JOIN Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID 
                                                         LEFT JOIN KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID 
                                                         LEFT JOIN LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID 
                                                         LEFT JOIN Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID
                                 WHERE " + GetSinhVienHocMonHocFilter(Lop_ID, MonHoc_ID, NamHoc_ID, HocKy_ID);
            List<string> lsFilter = new List<string>();

            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " AND " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        private string GetSinhVienHocMonHocFilter(int Lop_ID, int MonHoc_ID, int NamHoc_ID, int HocKy_ID)
        {
            string str = @"SinhVien.SinhVien_ID IN
                        (
                            SELECT t.SinhVien_ID FROM
                            (
                               (SELECT DangKyHocPhan.SinhVien_ID FROM DangKyHocPhan " + (Lop_ID > 0 ? "INNER JOIN LopSinhVien LopSinhVien1 ON LopSinhVien1.SinhVien_ID=DangKyHocPhan.SinhVien_ID " : "") + " WHERE DangKyHocPhan.MonHoc_ID=" + MonHoc_ID.ToString() + " AND DangKyHocPhan.NamHoc_ID=" + NamHoc_ID.ToString() + " AND DangKyHocPhan.HocKy_ID=" + HocKy_ID.ToString() + (Lop_ID > 0 ? " AND LopSinhVien1.Lop_ID=" + Lop_ID.ToString() : "") + @")
                                UNION
                               (SELECT DanhSachHocLai.SinhVien_ID FROM DanhSachHocLai WHERE DanhSachHocLai.MonHoc_ID=" + MonHoc_ID.ToString() + " AND DanhSachHocLai.NamHoc_ID=" + NamHoc_ID.ToString() + " AND DanhSachHocLai.HocKy_ID=" + HocKy_ID.ToString() + (Lop_ID > 0 ? " AND DanhSachHocLai.Lop_ID=" + Lop_ID.ToString() : "") + @")
                            )t
                        )";
            return str;
        }
        public DataSet SearchSinhVienWithDiemMonHoc(bool AllowLeftJoinSinhVienMonHoc, bool IsJoinToDiemMonHoc, bool IsJoinToDangKyHocPhan, bool IsJoinToSinhVienLopTinChi, int MonHoc_ID, int LoaiDiem_ID, int LanThi, int HocKy_ID, int NamHoc_ID, int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity, string Filter)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc,NganhHoc.TenNganhHoc,SinhVienMonHoc.SinhVienMonHoc_ID,SinhVienMonHoc.IsDat,DiemMonHoc.Diem,
                                                         DiemMonHoc.DiemMonHoc_ID,DiemMonHoc.LoaiDiem_ID,DiemMonHoc.LanThi,KhoaHoc.MaKhoaHoc,KhoaHoc.TenKhoaHoc,KhoaHoc.KhoaHoc_ID,SinhVienMonHoc.MonHoc_ID
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID " + (AllowLeftJoinSinhVienMonHoc ? "LEFT" : "INNER") + @" JOIN
                                                         SinhVienMonHoc ON SinhVien.SinhVien_ID=SinhVienMonHoc.SinhVien_ID
                                                         " + (MonHoc_ID > 0 ? " AND SinhVienMonHoc.MonHoc_ID=" + MonHoc_ID.ToString() : "") + @" 
                                                         " + (HocKy_ID > 0 ? " AND SinhVienMonHoc.HocKy_ID=" + HocKy_ID.ToString() : "") + @"
                                                         " + (NamHoc_ID > 0 ? " AND SinhVienMonHoc.NamHoc_ID=" + NamHoc_ID.ToString() : "") + @"
                                                         " + (IsJoinToDiemMonHoc ? @"
                                                         LEFT JOIN DiemMonHoc ON SinhVienMonHoc.SinhVienMonHoc_ID=DiemMonHoc.SinhVienMonHoc_ID
                                                         " + (LoaiDiem_ID > 0 ? " AND DiemMonHoc.LoaiDiem_ID=" + LoaiDiem_ID.ToString() : "") + @"
                                                         " + (LanThi > 0 ? " AND DiemMonHoc.LanThi=" + LanThi.ToString() : "") + @"
                                                         " : "") + @"
                                                         " + (IsJoinToDangKyHocPhan ? "INNER JOIN DangKyHocPhan ON SinhVien.SinhVien_ID=DangKyHocPhan.SinhVien_ID "
                                                           : "") + @"
                                                         " + (IsJoinToSinhVienLopTinChi ? "LEFT JOIN SinhVienLopTinChi ON SinhVien.SinhVien_ID=SinhVienLopTinChi.SinhVien_ID LEFT JOIN LopTinChi ON SinhVienLopTinChi.LopTinChi_ID=LopTinChi.LopTinChi_ID" + (HocKy_ID > 0 ? " AND LopTinChi.HocKy_ID=" + HocKy_ID.ToString() : "") + (NamHoc_ID > 0 ? " AND LopTinChi.NamHoc_ID=" + NamHoc_ID.ToString() : "") + (MonHoc_ID > 0 ? " AND LopTinChi.MonHoc_ID=" + MonHoc_ID.ToString() : "")
                                                           : "");

            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            if (MonHoc_ID > 0)
            {
                if (IsJoinToDangKyHocPhan)
                {
                    lsFilter.Add("DangKyHocPhan.MonHoc_ID=" + MonHoc_ID.ToString());
                }
            }
            if (HocKy_ID > 0)
            {
                if (IsJoinToDangKyHocPhan)
                {
                    lsFilter.Add("DangKyHocPhan.HocKy_ID=" + HocKy_ID.ToString());
                }
            }
            if (NamHoc_ID > 0)
            {
                if (IsJoinToDangKyHocPhan)
                {
                    lsFilter.Add("DangKyHocPhan.NamHoc_ID=" + NamHoc_ID.ToString());
                }
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (Filter.Trim().Length > 0)
                lsFilter.Add(Filter);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());

            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienChuaPhanLop(int NganhHoc_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc, Khoa.MaKhoa, Khoa.TenKhoa,0 as Lop_ID,'' as TenLop,'' as MaLop, LyLichSinhVien.DiemDuTuyen
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + (NganhHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID LEFT JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID LEFT JOIN 
                                                         LyLichSinhVien ON SinhVien.SinhVien_ID=LyLichSinhVien.SinhVien_ID
                                 WHERE
                                               SinhVien.SinhVien_ID NOT IN(SELECT SinhVien_ID FROM LopSinhVien Inner Join Lop  On LopSinhVien.Lop_ID=Lop.Lop_ID WHERE Lop.NganhHoc_ID=" + NganhHoc_ID.ToString() + " AND Lop.KhoaHoc_ID=" + KhoaHoc_ID.ToString() + @")
                                               AND SinhVien_ID NOT IN(SELECT SinhVien_ID FROM DanhSachBaoLuu)
                                    ";
            List<string> lsFilter = new List<string>();
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " AND " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchDataCommonUpdate(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, string TableUpdate, List<string> LsColumnUpdate, string ColumnLink, string Column_ID, bool IsInnerJoin, SinhVienEntity sinhVienEntity, string Filter)
        {
            for (int i = 0; i < LsColumnUpdate.Count; i++)
            {
                LsColumnUpdate[i] = TableUpdate + "." + LsColumnUpdate[i];
            }
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID,
                                                " + (LsColumnUpdate.Count > 0 ? string.Join(",", LsColumnUpdate.ToArray()) + "," : "") + TableUpdate + "." + Column_ID + " as " + Column_ID + @"Alias
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID " + (IsInnerJoin ? "INNER" : "LEFT") + @" JOIN 
                                                " + TableUpdate + " ON " + TableUpdate + "." + ColumnLink + "=SinhVien.SinhVien_ID " + (Filter.Length > 0 ? " AND " + Filter : "");
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }

            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienNopTien(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity, int NamHoc_ID, int HocKy_ID, DateTime TuNgay, DateTime DenNgay, bool IsFindByNgay, byte KieuThanhToan)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);

            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID,
                                                         SoCoSinhVien.SoCoSinhVien_ID,
                                                         (SELECT SUM(SoCoSinhVienChiTiet.SoTien)  FROM SoCoSinhVienChiTiet WHERE SoCoSinhVienChiTiet.SoCoSinhVien_ID=SoCoSinhVien.SoCoSinhVien_ID) as SoTien
                                FROM          SinhVien  INNER JOIN SoCoSinhVien ON SinhVien.SinhVien_ID=SoCoSinhVien.SinhVien_ID
                                                        " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID
                                ";
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            if (NamHoc_ID > 0)
            {
                lsFilter.Add("SoCoSinhVien.NamHoc_ID=" + NamHoc_ID.ToString());
            }
            if (HocKy_ID > 0)
            {
                lsFilter.Add("SoCoSinhVien.HocKy_ID=" + HocKy_ID.ToString());
            }
            if (IsFindByNgay)
            {
                lsFilter.Add("SoCoSinhVien.NgayNop>=@TuNgay");
                this.CreateCommandParameter(cmd, "@TuNgay", TuNgay);
                lsFilter.Add("SoCoSinhVien.NgayNop<=@DenNgay");
                this.CreateCommandParameter(cmd, "@DenNgay", DenNgay);
            }
            lsFilter.Add("SoCoSinhVien.KieuThanhToan=@KieuThanhToan");
            this.CreateCommandParameter(cmd, "@KieuThanhToan", KieuThanhToan);
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet TongHopDoiTuongTroCap(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity, int NamHoc_ID, int HocKy_ID)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            IDbCommand cmd = this.CreateCommand();
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            cmd.CommandText =
                @"SELECT 
                            temp_tt.* ,
                            (
                            case KieuTroCap
                             when 1 then PhaiNop*MucTroCap/100
                             else PhaiNop-MucTroCap
                            end
                            ) as MienGiam
                            FROM
                            (
                            SELECT 
                            temp_doituong.*,
                            (SELECT SUM(SoNoSinhVienChiTiet.SoTien) FROM SoNoSinhVienChiTiet INNER JOIN SoNoSinhVien ON SoNoSinhVienChiTiet.SoNoSinhVien_ID=SoNoSinhVien.SoNoSinhVien_ID WHERE SoNoSinhVien.SinhVien_ID=temp_doituong.SinhVien_ID AND SoNoSinhVienChiTiet.KhoanTien_ID=temp_doituong.KhoanTien_ID AND SoNoSinhVien.HocKy_ID=" + HocKy_ID.ToString() + " AND SoNoSinhVien.NamHoc_ID=" + NamHoc_ID.ToString() + @")as PhaiNop
                            FROM
                            (
                            SELECT     
                            DISTINCT SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, SinhVien.DienThoai, SinhVien.GhiChu,SinhVien.GioiTinh, SinhVien.NgaySinh,
                            DoiTuongTroCap.DoiTuongTroCap_ID,DoiTuongTroCap.LoaiDoiTuongTroCap_ID,
                            LoaiDoiTuongTroCap.TenLoaiDoiTuong,
                            KhoanTien.TenKhoanTien,KhoanTien.KhoanTien_ID,
                            KieuHuongTroCap.MucTroCap,KieuHuongTroCap.KieuTroCap
                            FROM  
                            DoiTuongTroCap INNER JOIN SinhVien ON DoiTuongTroCap.SinhVien_ID = SinhVien.SinhVien_ID
                            INNER JOIN LoaiDoiTuongTroCap ON DoiTuongTroCap.LoaiDoiTuongTroCap_ID=LoaiDoiTuongTroCap.LoaiDoiTuongTroCap_ID
                            INNER JOIN KieuHuongTroCap ON LoaiDoiTuongTroCap.LoaiDoiTuongTroCap_ID = KieuHuongTroCap.LoaiDoiTuongTroCap_ID 
                            INNER JOIN KhoanTien ON KieuHuongTroCap.KhoanTien_ID = KhoanTien.KhoanTien_ID
                            " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID
                            " + (lsFilter.Count > 0 ? " WHERE " + string.Join(" AND ", lsFilter.ToArray()) : "") + @"
                            )temp_doituong
                            )temp_tt
                WHERE phainop>0                        
                            ";
            return this.SelectDS(cmd);
        }
        public DataSet SearchDanhSachHocBong(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity, int NamHoc_ID, int HocKy_ID)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);

            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID,
                                                         DanhSachHocBong.DanhSachHocBong_ID,MucHocBong.TenMuc,MucHocBong.SoTien
                                FROM          SinhVien  INNER JOIN DanhSachHocBong ON SinhVien.SinhVien_ID=DanhSachHocBong.SinhVien_ID
                                                        INNER JOIN MucHocBong ON DanhSachHocBong.MucHocBong_ID=MucHocBong.MucHocBong_ID
                                                        " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID
                                ";
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            if (NamHoc_ID > 0)
            {
                lsFilter.Add("DanhSachHocBong.NamHoc_ID=" + NamHoc_ID.ToString());
            }
            if (HocKy_ID > 0)
            {
                lsFilter.Add("DanhSachHocBong.HocKy_ID=" + HocKy_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienUsers(List<int> lsSinhVien_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText =
                @"SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, SinhVien.DienThoai, SinhVien.GhiChu, 
                         SinhVien.GioiTinh, SinhVien.NgaySinh, Users.UserName, Users.DisplayName, Users.User_ID, Users.Password, Users.UserType,
                         Users.Phone, Users.Employee_ID, Users.IsOnline, Users.Actived
                  FROM   SinhVien LEFT JOIN
                         Users ON Users.Employee_ID = SinhVien.SinhVien_ID
                         ";
            cmd.CommandText += " WHERE SinhVien_ID IN(" + string.Join(",", lsSinhVien_ID.ConvertAll<string>(Convert.ToString).ToArray()) + ")";
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienLopHocPhan(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, List<int> lsLopHocPhan_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID,
                                               LopTinChi.MaLop as MaLopTinChi,LopTinChi.LopTinChi_ID,SinhVienLopTinChi.TrangThai as TrangThaiDangKy,SinhVienLopTinChi.SinhVienLopTinChi_ID,SinhVienLopTinChi.NgayDangKy
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID INNER JOIN
                                                         SinhVienLopTinChi ON SinhVien.SinhVien_ID=SinhVienLopTinChi.SinhVien_ID INNER JOIN 
                                                         LopTinChi ON SinhVienLopTinChi.LopTinChi_ID=LopTinChi.LopTinChi_ID
                                                            ";
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            if (lsLopHocPhan_ID.Count > 0)
            {
                lsFilter.Add("SinhVienLopTinChi.LopTinChi_ID IN(" + string.Join(",", lsLopHocPhan_ID.ConvertAll<string>(Convert.ToString).ToArray()) + ")");
            }

            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SearchSinhVienWithDiemTongKetHocKy(int HocKy_ID, int NamHoc_ID, int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID,SinhVienHocKy.DiemTongKet, SinhVienHocKy.SinhVienHocKy_ID
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID LEFT JOIN
                                                SinhVienHocKy ON SinhVien.SinhVien_ID=SinhVienHocKy.SinhVien_ID ";
            List<string> lsFilter = new List<string>();

            if (HocKy_ID > 0)
            {
                lsFilter.Add("SinhVienHocKy.HocKy_ID=" + HocKy_ID.ToString());
            }
            if (NamHoc_ID > 0)
            {
                lsFilter.Add("SinhVienHocKy.NamHoc_ID=" + NamHoc_ID.ToString());
            }
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SelectDataLookUpTable(string TableLookUp, string ValueMember, string DisplayMember)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = "SELECT " + ValueMember + "," + DisplayMember + " FROM " + TableLookUp;
            return this.SelectDS(cmd);
        }
        public bool UpdateDataCommon(string TableUpdate, string Column_ID, string ColumnUpdate, object Value, object ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = "Update " + TableUpdate + " Set " + ColumnUpdate + "=@UpdateValue WHERE " + Column_ID + "=@ID";
            this.CreateCommandParameter(cmd, "@UpdateValue", Value);
            this.CreateCommandParameter(cmd, "@ID", ID);
            return this.ExcuteNoneQuery(cmd);
        }
        public bool DeleteSinhVienByListSinhVien_ID(List<int> lsSinhVien_ID)
        {
            IDbCommand cmd = this.CreateDeleteAllCommand();
            cmd.CommandText += " WHERE SinhVien_ID IN(" + string.Join(",", lsSinhVien_ID.ConvertAll<string>(Convert.ToString).ToArray()) + ")";
            return this.ExcuteNoneQuery(cmd);
        }
        public DataSet SearchSinhVienHocLai(int NamHoc_ID, int HocKy_ID, int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT        SinhVien.SinhVien_ID, SinhVien.MaSinhVien, SinhVien.TenSinhVien, SinhVien.DiaChi, SinhVien.Email, SinhVien.SoCMND, 
                                                         SinhVien.DienThoai, SinhVien.GhiChu, SinhVien.GioiTinh, SinhVien.NgaySinh, Lop.MaLop, Lop.TenLop,Lop.Lop_ID,LopSinhVien.LopSinhVien_ID,KhoaHoc.KhoaHoc_ID,KhoaHoc.MaKhoaHoc, KhoaHoc.TenKhoaHoc, 
                                                         NganhHoc.NganhHoc_ID,NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc,Khoa.Khoa_ID,Khoa.MaKhoa, Khoa.TenKhoa,PhanNganhSinhVien.PhanNganhSinhVien_ID
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID
                                                         INNER JOIN DanhSachDeNghiLuuBan ON SinhVien.SinhVien_ID=DanhSachDeNghiLuuBan.SinhVien_ID
                                                         LEFT JOIN SinhVienHocKy ON SinhVienHocKy.SinhVien_ID=SinhVienHocKy.SinhVien_ID AND SinhVienHocKy.HocKy_ID=" + HocKy_ID.ToString() + " AND SinhVienHocKy.NamHoc_ID=" + NamHoc_ID.ToString();
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            if (NamHoc_ID > 0)
            {
                lsFilter.Add("DanhSachDeNghiLuuBan.NamHoc_ID=" + NamHoc_ID.ToString());
            }
            if (HocKy_ID > 0)
            {
                lsFilter.Add("DanhSachDeNghiLuuBan.HocKy_ID=" + HocKy_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSDataBySQL(string SQL, Dictionary<string, object> Parameters)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = SQL;
            foreach (string st in Parameters.Keys)
            {
                this.CreateCommandParameter(cmd, st, Parameters[st]);
            }
            return this.SelectDS(cmd);
        }
        public DataSet ThongKeSinhVien(int Khoa_ID, int NganhHoc_ID, int Lop_ID, int KhoaHoc_ID, SinhVienEntity sinhVienEntity, string TableJoin, string DisplayColName, string JoinKey, bool IsInnerJoin)
        {
            bool IsJoinToPhanNganh = (Khoa_ID > 0 || NganhHoc_ID > 0 || KhoaHoc_ID > 0);
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @" SELECT COUNT(SinhVien.SinhVien_ID) as SOLUONG,
                               " + (TableJoin.Length > 0 ? TableJoin + "." + DisplayColName : DisplayColName) + @"
                                 FROM          SinhVien " + (IsJoinToPhanNganh ? " INNER " : " LEFT ") + @" JOIN
                                                         PhanNganhSinhVien ON SinhVien.SinhVien_ID = PhanNganhSinhVien.SinhVien_ID " + ((NganhHoc_ID > 0) || (Khoa_ID > 0) ? "INNER" : "LEFT") + @" JOIN
                                                         NganhHoc ON PhanNganhSinhVien.NganhHoc_ID = NganhHoc.NganhHoc_ID " + (Khoa_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID " + (KhoaHoc_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         KhoaHoc ON PhanNganhSinhVien.KhoaHoc_ID = KhoaHoc.KhoaHoc_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         LopSinhVien ON SinhVien.SinhVien_ID = LopSinhVien.SinhVien_ID " + (Lop_ID > 0 ? "INNER" : "LEFT") + @" JOIN
                                                         Lop ON LopSinhVien.Lop_ID = Lop.Lop_ID ";
            if (TableJoin.Length > 0)
            {
                cmd.CommandText += (IsInnerJoin ? "INNER" : "LEFT") + " JOIN " + TableJoin + " ON SinhVien.SinhVien_ID=" + TableJoin + "." + JoinKey + " ";
            }
            List<string> lsFilter = new List<string>();
            if (Khoa_ID > 0)
            {
                lsFilter.Add("Khoa.Khoa_ID=" + Khoa_ID.ToString());
            }
            if (NganhHoc_ID > 0)
            {
                lsFilter.Add("NganhHoc.NganhHoc_ID=" + NganhHoc_ID.ToString());
            }
            if (KhoaHoc_ID > 0)
            {
                lsFilter.Add("KhoaHoc.KhoaHoc_ID=" + KhoaHoc_ID.ToString());
            }
            if (Lop_ID > 0)
            {
                lsFilter.Add("Lop.Lop_ID=" + Lop_ID.ToString());
            }
            CreateFilterSinhVien(lsFilter, cmd, sinhVienEntity);
            if (lsFilter.Count > 0)
                cmd.CommandText += " WHERE " + string.Join(" AND ", lsFilter.ToArray());
            cmd.CommandText += " GROUP BY " + (TableJoin.Length > 0 ? TableJoin + "." + DisplayColName : DisplayColName);
            return this.SelectDS(cmd);
        }
    }
    /*****************************************************************************************************/
    /**********************************END SinhVienDAO*****************************************/
    /*****************************************************************************************************/
}

